package com.plian.system.service.dsh.impl;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.plian.Tools.*;
import com.plian.Tools.pagedata.PageData;
import com.plian.system.bean.dsh.DshStaffManagementBean;
import com.plian.system.bean.staff.*;
import com.plian.system.constant.baseinfo.ValueSetConstant;
import com.plian.system.constant.sys.FormStatusConstant;
import com.plian.system.constant.sys.FormTypeConstant;
import com.plian.system.dao.sys.orgzation.OrgzationDao;
import com.plian.system.dto.dsh.DshStaffManagementDTO;
import com.plian.system.entity.dsh.DshStaffManagement;
import com.plian.system.entity.hr.staff.*;
import com.plian.system.excel.ImportExcelUtil;
import com.plian.system.excel.ImportResult;
import com.plian.system.mapper.dsh.DshStaffManagementMapper;
import com.plian.system.model.CommonCode;
import com.plian.system.model.R;
import com.plian.system.mybatis.base.BaseFlowService;
import com.plian.system.mybatis.base.BaseServiceImpl;
import com.plian.system.mybatis.search.SearchParamEntiy;
import com.plian.system.mybatis.search.TokenSearch;
import com.plian.system.mybatis.support.Condition;
import com.plian.system.mybatis.support.Query;
import com.plian.system.service.bt.baseinfo.BaseInfoService;
import com.plian.system.service.dsh.IDshStaffManagementService;
import com.plian.system.service.dsh.check.DshStaffManagementCheckData;
import com.plian.system.service.dsh.check.DshStaffManagementCheckDataSon;
import com.plian.system.service.hr.dept.IDeptService;
import com.plian.system.service.hr.staff.*;
import com.plian.system.service.sys.code.IFormCodeService;
import com.plian.system.service.sys.orgzation.OrgzationService;
import com.plian.system.service.wf.FormWorkflowService;
import com.plian.system.service.wfTwo.WorkflowTwoService;
import com.plian.system.vo.dsh.DshStaffExport;
import com.plian.system.vo.dsh.DshStaffManagementVO;
import com.plian.system.vo.hr.staff.*;
import com.plian.system.vo.report.HeaderVO;
import com.plian.system.vo.report.ReportReturnVO;
import com.plian.system.vo.report.ReportVO;
import com.plian.system.wrapper.dsh.DshStaffManagementWrapper;
import com.plian.system.wrapper.hr.staff.*;
import lombok.AllArgsConstructor;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.core.env.Environment;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.AsyncResult;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;
import java.util.concurrent.Future;
import java.util.stream.Collectors;

import static com.plian.system.constant.sys.BaseConstant.BASE64_KEY;

;

/**
 * 监事会 服务实现类
 *
 * @author gangan
 * @since 2019-11-12
 */
@Service
@AllArgsConstructor
public class DshStaffManagementServiceImpl extends BaseServiceImpl<DshStaffManagementMapper, DshStaffManagement> implements IDshStaffManagementService, BaseFlowService {

    private IStaffFilesService staffFilesService;

    private StaffFilesWrapper staffFilesWrapper;

    private DshStaffManagementWrapper dshStaffManagementWrapper;


    private IFormCodeService formCodeService;

    private FormWorkflowService formWorkflowService;
    private WorkflowTwoService workflowTwoService;

    private IAppointRemoveService appointRemoveService;
    private IEducationDegreeService educationDegreeService;
    private IRewardsPunishmentsService rewardsPunishmentsService;
    private IAnnualAssessmentService annualAssessmentService;
    private ISocialRelationsService socialRelationsService;
    private IPractitionerCertificateService practitionerCertificateService;
    private ITechnicalQualificationService technicalQualificationService;
    private ITrainService trainService;
    private IOverseasMembersService overseasMembersService;
    private BaseInfoService baseInfoService;
    private OrgzationDao orgzationDao;
    private IDeptService deptService;
    private OrgzationService orgzationService;
    private Environment environment;
    private AppointRemoveWrapper appointRemoveWrapper;
    private EducationDegreeWrapper educationDegreeWrapper;
    private RewardsPunishmentsWrapper rewardsPunishmentsWrapper;
    private AnnualAssessmentWrapper annualAssessmentWrapper;
    private SocialRelationsWrapper socialRelationsWrapper;
    private PractitionerCertificateWrapper practitionerCertificateWrapper;
    private TechnicalQualificationWrapper technicalQualificationWrapper;
    private TrainWrapper trainWrapper;
    private OverseasMembersWrapper overseasMembersWrapper;

    private static final String appointRemoveErrMsg = "任免记录";
    private static final String educationDegreeErrMsg = "学历学位";
    private static final String workExperienceErrMsg = "工作经历";
    private static final String rewardsPunishmentsErrMsg = "奖惩情况";
    private static final String annualAssessmentErrMsg = "年度考核结果";
    private static final String socialRelationsErrMsg = "家庭及社会关系";
    private static final String practitionerCertificateErrMsg = "从业资格证书";
    private static final String technicalQualificationErrMsg = "专业技术资格";
    private static final String trainErrMsg = "近五年三个月以上培训情况";
    private static final String overseasMembersErrMsg = "侨眷情况";


    //名称转cod 用参照字典（参照key,<name,id>）(需确保每个名字唯一)
    private static HashMap<String, Map<String, String>> nameMap = new HashMap<>();
    //部门参照(所属企业id,<部门名称,部门id>)
    private static HashMap<String, Map<String, Long>> deptUnitMap = new HashMap<>();
    //部门参照(所属企业id,<部门名称,部门Code>)
    private static HashMap<String, Map<String, String>> deptUnitCodeMap = new HashMap<>();

    // 字典map
    private static HashMap<String, List<String>> codeMap = new HashMap<>();

    // report接口 entity类转vo使用
    private static Map<String, String> allCodeMap = new HashMap<>();
    private static Map<Long, String> deptMap = new HashMap<>();
    private static Map<String, String> orgMap = new HashMap<>();
    private static Map<String, String> fileNameMap = new HashMap<>();


    private static final String managementCategoryCode = "027";

    @Override
    public ReportReturnVO report(DshStaffManagementDTO supervisorDTO) {
        ReportReturnVO reportReturnVO = new ReportReturnVO();
        ReportVO reportVO = new ReportVO();

        reportVO
                .addHeader(HeaderVO.builder().key("companyName").title("单位").build())
                .addHeader(HeaderVO.builder().key("staffName").title("姓名").build())
                .addHeader(HeaderVO.builder().key("isOutSupervisorName").title("是否外部董事").build())
                .addHeader(HeaderVO.builder().key("supervisorPositionName").title("董事会职务").build())
                .addHeader(HeaderVO.builder().key("genderName").title("性别").build())
                .addHeader(HeaderVO.builder().key("birthday").title("出生日期").build())
                .addHeader(HeaderVO.builder().key("nationName").title("民族").build())
                .addHeader(HeaderVO.builder().key("politicCountenanceName").title("政治面貌").build())
//                .addHeader(HeaderVO.builder().key("staffCategoryName").title("人员类别").build())
                .addHeader(HeaderVO.builder().key("managementCategoryName").title("管理类别").build())
                .addHeader(HeaderVO.builder().key("talentCategory").title("人才类别").build())
                .addHeader(HeaderVO.builder().key("nativePlace").title("籍贯").build())
                .addHeader(HeaderVO.builder().key("birthPlace").title("出生地").build())
                .addHeader(HeaderVO.builder().key("workTime").title("参加工作时间").build())
                .addHeader(HeaderVO.builder().key("technicalPosition").title("专业技术职称").build())
                .addHeader(HeaderVO.builder().key("educationalName").title("最高学历").build())
                .addHeader(HeaderVO.builder().key("degreeName").title("最高学位").build())
                .addHeader(HeaderVO.builder().key("staffCode").title("身份证号").build())
                .addHeader(HeaderVO.builder().key("appointmentTime").title("任职时间").build());

        List<DshStaffManagement> directors = baseMapper.reportDataPage(supervisorDTO.getCompanyIds(),
                supervisorDTO.getGenderIds(),
                supervisorDTO.getSupervisorPositionIds(),
                supervisorDTO.getIsOutSupervisor(),
                supervisorDTO.getStaffName(),
                supervisorDTO.getJobNameId(),
                supervisorDTO.getEducationalIds(),
                supervisorDTO.getTechnicalPosition(),
                supervisorDTO.getCompanyInitiationTimeStart(),
                supervisorDTO.getCompanyInitiationTimeEnd(),
                supervisorDTO.getWorkTimeStart(),
                supervisorDTO.getWorkTimeEnd(),
                supervisorDTO.getPoliticCountenanceIds(),
                supervisorDTO.getIsReserveCadre(),
                supervisorDTO.getIsCorporateTalent(),
                supervisorDTO.getManagementCategoryIds());
        List<DshStaffManagementVO> list = dshStaffManagementWrapper.entityToVO(directors);


        //给所有数据按照单位编码升序，人员编码升序
        list = list.parallelStream().sorted(Comparator.comparing(DshStaffManagementVO::getCompanyCode).thenComparing(DshStaffManagementVO::getCreateTime)).collect(Collectors.toList());
        List<DshStaffManagementVO> resultData = new ArrayList<>() ;
        String companyNameSearchId = "";
        int count = 0;
        int sum = 0;
        for(int i =0;i< list.size();i++){
            if(!companyNameSearchId.equals(list.get(i).getCompanyId())){
                if(i>0){
                    DshStaffManagementVO Total = new DshStaffManagementVO();
                    Total.setCompanyName("小计：" + count);
                    resultData.add(Total);
                    sum+=count;
                }
                companyNameSearchId = list.get(i).getCompanyId();
                count = 1;
            }else{
                count++;
            }
            StaffFilesVO staffFilesVO = staffFilesWrapper.entityToVO(list.get(i).getStaffFilesVO());
            list.get(i).setNationName(staffFilesVO.getNationName());
            list.get(i).setPoliticCountenanceName(staffFilesVO.getPoliticCountenanceName());
            list.get(i).setManagementCategoryName(staffFilesVO.getManagementCategoryName());
            list.get(i).setTalentCategory(staffFilesVO.getTalentCategory());
            list.get(i).setNativePlace(staffFilesVO.getNativePlace());
            list.get(i).setBirthPlace(staffFilesVO.getBirthPlace());
            list.get(i).setWorkTime(staffFilesVO.getWorkTime());
            list.get(i).setTechnicalPosition(staffFilesVO.getTechnicalPosition());
            list.get(i).setEducationalName(staffFilesVO.getEducationalName());
            list.get(i).setDegreeName(staffFilesVO.getDegreeName());
            list.get(i).setStaffCode(staffFilesVO.getStaffCode());
            resultData.add(list.get(i));
        }
        sum+=count;
        DshStaffManagementVO LastTotal = new DshStaffManagementVO();
        LastTotal.setCompanyName("小计：" + count);
        resultData.add(LastTotal);

        DshStaffManagementVO SumTotal = new DshStaffManagementVO();
        SumTotal.setCompanyName("总计：" + sum);
        resultData.add(SumTotal);


        reportVO.setData(resultData);
        reportReturnVO.setGrids(reportVO);
        return reportReturnVO;
    }

    @Override
    @Transactional(rollbackFor=RuntimeException.class)
    public R submit(DshStaffManagement supervisor) {
        if (supervisor.getId() == null){
            supervisor.setFormType(String.valueOf(FormTypeConstant.DSH_STAFF_FORM));
        }
        StaffFilesVO staffFilesVO = supervisor.getStaffFilesVO();
        if (supervisor.getIsOutSupervisor() == 1 && Optional.ofNullable(staffFilesVO).isPresent()){
            // 根据身份证查重
            StaffFiles staffFiles = new StaffFiles();
            BeanUtils.copyProperties(staffFilesVO, staffFiles);

            StaffFiles will = staffFilesService.searchStaffByStaffCode(staffFiles.getDocumentNumber());
            StaffFiles old = null;
            if(null != staffFiles.getId()){
                old = staffFilesService.getById(staffFiles.getId());
            }
            if(will != null && null != old){
                String willDecrypted = EncryptUtil.decryptBase64(will.getStaffCode(), BASE64_KEY);
                String oldDecrypted = EncryptUtil.decryptBase64(old.getStaffCode(), BASE64_KEY);
                if(!oldDecrypted.equals(willDecrypted)){
                    return new R("证件号已存在");
                }
            }
            staffFiles.setIsOut(2);
            staffFiles.setPersonnelType(2);
            if (StringUtil.isNotBlank(staffFiles.getStaffCode())) {
                staffFiles.setStaffCode(EncryptUtil.encryptBase64(staffFiles.getStaffCode(), BASE64_KEY));
            }
            if (StringUtil.isNotBlank(staffFiles.getDocumentNumber())) {
                staffFiles.setDocumentNumber(EncryptUtil.encryptBase64(staffFiles.getDocumentNumber(), BASE64_KEY));
            }
            boolean result = staffFilesService.saveOrUpdate(staffFiles);
            if(result) {
                staffFilesVO.setId(staffFiles.getId());
                staffFilesService.saveChild(staffFilesVO);
            }
        }else if(supervisor.getIsOutSupervisor() == 2 && Optional.ofNullable(staffFilesVO).isPresent()){
            StaffFiles staffFiles = new StaffFiles();
            BeanUtils.copyProperties(staffFilesVO, staffFiles);
            staffFiles.setIsOut(1);
//            staffFiles.setPersonnelType(2);
            if (StringUtil.isNotBlank(staffFiles.getStaffCode())) {
                staffFiles.setStaffCode(EncryptUtil.encryptBase64(staffFiles.getStaffCode(), BASE64_KEY));
            }
            if (StringUtil.isNotBlank(staffFiles.getDocumentNumber())) {
                staffFiles.setDocumentNumber(EncryptUtil.encryptBase64(staffFiles.getDocumentNumber(), BASE64_KEY));
            }
            boolean result = staffFilesService.saveOrUpdate(staffFiles);
            if(result) {
                staffFilesVO.setId(staffFiles.getId());
                staffFilesService.saveChild(staffFilesVO);
            }
        }
        supervisor.setStaffFilesId(staffFilesVO.getId());
        supervisor.setCompanyId(staffFilesVO.getCompanyNameId());
        supervisor.setBirthday(staffFilesVO.getBirthday());
        supervisor.setGenderId(staffFilesVO.getGenderId());
        supervisor.setStaffName(staffFilesVO.getStaffName());
        supervisor.setAppointmentTime(staffFilesVO.getCompanyInitiationTime());
        boolean result= saveOrUpdate(supervisor);
//        supervisor.setStatus(FormStatusConstant.UNREVIEWED);
        supervisor.setCode(formCodeService.getCode(FormTypeConstant.DSH_STAFF_FORM));
        supervisor.setFormType(String.valueOf(FormTypeConstant.DSH_STAFF_FORM));
        HashMap<String, Object> processVariable = new HashMap<>();
        formWorkflowService.startProcessAndSimulate(String.valueOf(supervisor.getId()), supervisor.getFormType(), processVariable);

        if(result){
            if(supervisor.getStatus() == 3){
                // 反审核
                goBackToStash(String.valueOf(supervisor.getId()));
                workflowTwoService.goBackToStash(String.valueOf(supervisor.getId()), FormTypeConstant.HR_STAFFFILES_FORM);
            }
        }
        return new R(CommonCode.SUCCESS);
    }

    @Override
    public R<DshStaffManagementVO> getDetailObj(DshStaffManagementVO supervisorVO) {
        if(!Optional.ofNullable(supervisorVO).isPresent()){
            return new R(CommonCode.FAIL);
        }
        StaffFilesVO staffFilesVO = staffFilesWrapper.entityToVO(staffFilesService.getById(supervisorVO.getStaffFilesId()));
        supervisorVO.setStaffFilesVO(staffFilesService.getDetailObj(staffFilesVO).getPageData());
        return R.data(supervisorVO);
    }

    @Override
    @Transactional(rollbackFor=RuntimeException.class)
    public boolean deleteLogic(List<Long> ids) {
        List<Long> delStaffFilesIds = new ArrayList<>();
        for (Long id : ids) {
            DshStaffManagement supervisor = getById(id);
            if (supervisor == null) {
                return false;
            }

            if (Optional.ofNullable(supervisor.getIsOutSupervisor()).isPresent()
                    && supervisor.getIsOutSupervisor() == 1
                    && Optional.ofNullable(supervisor.getStaffFilesId()).isPresent()){
                delStaffFilesIds.add(supervisor.getStaffFilesId());
            }
        }

        boolean result = super.deleteLogic(ids);
        if (result && CollectionUtil.isNotEmpty(delStaffFilesIds)){
            staffFilesService.deleteLogic(delStaffFilesIds);
        }
        return result;
    }

    @Override
    public HashMap<String, Object> commit(String id) {
        handleStatus(Long.valueOf(id), FormStatusConstant.FLOW);

        //将需要提交的流程变量返回，用于工作流侧
        return new HashMap<String, Object>();
    }

    @Override
    public Boolean finish(String id) {
        /**
         * 设置审批人和审批时间
         */
        DshStaffManagement entity = getById(id);
        entity.setApprovedUser(TokenUtil.getTokenUserId());
        Date now = DateUtil.now();
        entity.setApprovedTime(now);
        updateById(entity);

        return handleStatus(Long.valueOf(id), FormStatusConstant.FINISH);
    }

    @Override
    public Boolean reject(String id, int formState, String comment) {
        return handleStatus(Long.valueOf(id), formState);
    }

    @Override
    public void goBackToStash(String formId) {
        handleStatus(Long.valueOf(formId), FormStatusConstant.UNREVIEWED);
    }

    @Override
    public List<PageData> findByIds(List<String> formIds) {
        List<DshStaffManagement> list = list(new LambdaQueryWrapper<DshStaffManagement>().in(DshStaffManagement::getId, Func.toLongList(StringUtils.join(formIds, StringPool.COMMA))));
        List<DshStaffManagementVO> vos = dshStaffManagementWrapper.entityToVO(list);
        List<PageData> pageDataList = new ArrayList<>();
        try {
            if (CollectionUtil.isNotEmpty(vos)) {
                for (DshStaffManagementVO vo : vos) {
                    PageData pd = new PageData();
                    pd.put("id", vo.getId());
                    pd.put("UNITNAME", vo.getCompanyName());
                    pd.put("APPLICANTNAME", vo.getCreateUserName());
                    pd.put("applicantId", vo.getCreateUser());
                    pd.put("applyOrganizationId", vo.getCompanyId());
                    pd.put("formType", FormTypeConstant.DSH_STAFF_FORM);
                    pd.put("orgName", vo.getCompanyName());
                    pageDataList.add(pd);
                }
            }
            return pageDataList;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
        }
        return pageDataList;
    }

    @Override
    public List<Integer> getFormType() {
        return Collections.singletonList(FormTypeConstant.DSH_STAFF_FORM);
    }

    private Boolean handleStatus(Long id, int updateStatus) {
        try {
            DshStaffManagement entity = getById(id);
            entity.setStatus(updateStatus);
            updateById(entity);
//            if(null != entity.getStaffFilesId()){
//                StaffFiles staff = staffFilesService.getById(entity.getStaffFilesId());
//                if(null != staff){
//                    staff.setStatus(updateStatus);
//                    staffFilesService.updateById(staff);
//                }
//            }
            return true;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return false;
        }
    }

    /**
     * 根据编号加单位查找员工
     *
     * @param staffCode 编号
     * @return 员工
     */
    private StaffFiles searchStaffByStaffCode(String staffCode) {
        return staffFilesService.getOne(new LambdaQueryWrapper<StaffFiles>().eq(StaffFiles::getStaffCode, EncryptUtil.encryptBase64(staffCode, BASE64_KEY))
                .eq(StaffFiles::getCompanyNameId, TokenUtil.getTokenOrgId()));
    }

    private void initMap() {
        //注：以下key值与实际配置的参照无关联，仅为了方便理解使用了相同名称
        //董事职务
		codeMap.put("directorShipRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.DIRECTORSHIP).keySet()));
        //证件类型
        codeMap.put("documentTypeRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.DOCUMENT_TYPE).keySet()));
        //性别
        codeMap.put("genderRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.GENDER).keySet()));
        //民族
        codeMap.put("nationRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.NATION).keySet()));
        //政治面貌
        codeMap.put("politicCountenanceRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.POLITIC_COUNTENANCE).keySet()));
        //国籍
        codeMap.put("nationalityRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.NATIONALITY).keySet()));
        //婚姻状态
        codeMap.put("maritalStatusRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.MARITAL_STATUS).keySet()));
        //最高学历
        codeMap.put("educationalRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.EDUCATION).keySet()));
        //最高学位
        codeMap.put("degreeRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.DEGREE).keySet()));
        //管理类别
        codeMap.put("managementCategoryRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.MANAGEMENT_CATEGORY).keySet()));
        //编制类型
        codeMap.put("compilationTypeRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.COMPILATION_TYPE).keySet()));
        //人员类型
        codeMap.put("staffTypeRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.STAFF_TYPE).keySet()));
        //人员类别
        codeMap.put("staffCategoryRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.STAFF_CATEGORY).keySet()));
        //职务类别
        codeMap.put("jobCategoryRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.JOB_CATEGORY).keySet()));
        //职级
        codeMap.put("rankRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.RANK).keySet()));
        //职等
        codeMap.put("gradePositionsRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.GRADE_POSITIONS).keySet()));
        //异动事件
        codeMap.put("changeEventsRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.CHANGE_EVENTS).keySet()));
        //异动类型
        codeMap.put("changeTypeRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.CHANGE_TYPE).keySet()));
        // 健康状况
        codeMap.put("healthConditionRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.HEALTH_CONDITION).keySet()));

        try {
            List<PageData> allOrgs = orgzationDao.list(null);
            //单位
            if (CollectionUtil.isNotEmpty(allOrgs)) {
                HashMap<String, String> orgMap = new HashMap<>();
                for (PageData pageData : allOrgs) {
                    orgMap.put((String) pageData.get("name"), (String) pageData.get("id"));
                }

                nameMap.put("unitRef", orgMap);
            }

            //  部门
            allOrgs.parallelStream().forEach(pd -> deptUnitMap.put((String) pd.get("id"), new HashMap<>()));
            allOrgs.parallelStream().forEach(pd -> deptUnitCodeMap.put((String) pd.get("id"), new HashMap<>()));
            deptService.list().parallelStream().forEach(x -> {
                //针对服务器可能存在部门未删除，但对应单位已删除的脏数据问题，每次进行对应key校验
                if (deptUnitMap.containsKey(x.getDeptAffiliation())) {
                    deptUnitMap.get(x.getDeptAffiliation()).put(x.getDeptName(), x.getId());
                }
                if (deptUnitCodeMap.containsKey(x.getDeptAffiliation())) {
                    deptUnitCodeMap.get(x.getDeptAffiliation()).put(x.getDeptName(), x.getDeptCode());
                }
            });

        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
        }

        //子表使用参照
        //教育类型
        codeMap.put("educationTypeRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.EDUCATION_TYPE).keySet()));
        //专业技术职务级别
        codeMap.put("technicalTitleLevelRef", new ArrayList<>(baseInfoService.getChildMap(ValueSetConstant.TECHNICAL_TITLE_LEVEL).keySet()));
    }

    /**
     * 导入
     *
     * @return object
     */
    @Override
    @Async("ttlExecutor")
    public Future<ImportResult> importExcel(String progressId, InputStream inputStream, Boolean isCover) {
        String progress = "progress";
        String key = progressId + "#half:hour";
        try {
            List<String> codeList = new ArrayList<>();
            byte[] bytes = IOUtils.toByteArray(inputStream);

            inputStream = new ByteArrayInputStream(bytes);

            List<Object> staffReadExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(1).headRowNumber(1).head(DshStaffManagementBean.class)
                            .registerReadListener(new PageReadListener<>(list -> staffReadExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(staffReadExcels);
            int size = staffReadExcels.size();
            //在所有校验前统一初始化所有字典
            initMap();
            //获取当前单位现有所有人事数据列表
            Query query = new Query();
            query.setFlag(1);
            QueryWrapper<DshStaffManagement> queryWrapper = SearchParamEntiy.getQueryWrapper(
                    Condition.getQueryWrapper(new DshStaffManagement()), query.getSearch()
            );
            TokenSearch<DshStaffManagement> tokenSearch = new TokenSearch<>();

            tokenSearch.addTokenQuery(queryWrapper, query);
            List<DshStaffManagement> dshList = this.list(queryWrapper);
            List<StaffFiles> staffFilesList = new ArrayList<>();
            if(CollectionUtil.isNotEmpty(dshList)){
                List<Long> staffIds = dshList.stream().map(DshStaffManagement::getStaffFilesId).collect(Collectors.toList());
                staffFilesList = staffFilesService.list(new LambdaQueryWrapper<StaffFiles>().in(StaffFiles::getId, staffIds));
            }
            //系统现有编码检查
            ImportResult importResult = DshStaffManagementCheckData.checkStaffData(staffReadExcels, staffFilesList, key, codeMap, nameMap, deptUnitMap, deptUnitCodeMap, isCover);
            if (!importResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, importResult.getMsg());
                return new AsyncResult<>(importResult);
            }
            for (Object object : staffReadExcels) {
                DshStaffManagementBean bean = (DshStaffManagementBean) object;
                String code = bean.getStaffCode();
                codeList.add(code);
            }
            //任免记录
            inputStream.reset();
            List<Object> appoiontExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(3).headRowNumber(1).head(AppointRemoveExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> appoiontExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(appoiontExcels);
            ImportResult appointResult = DshStaffManagementCheckDataSon.checkAppointRemove(appoiontExcels);
            if (!appointResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, appointResult.getMsg());
                return new AsyncResult<>(appointResult);
            }
            //学历学位子表
            inputStream.reset();
            List<Object> educationDegreeExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(4).headRowNumber(1).head(EducationDegreeExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> educationDegreeExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(educationDegreeExcels);
            ImportResult educationDegreeResult = DshStaffManagementCheckDataSon.checkEducationDegree(educationDegreeExcels, codeMap);
            if (!educationDegreeResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, educationDegreeResult.getMsg());
                return new AsyncResult<>(educationDegreeResult);
            }
            //工作经历
//			inputStream.reset();
//			List<Object> workExperienceExcels = EasyExcelFactory.read(
//				inputStream,new Sheet(6,1, WorkExperienceExcelBean.class)
//			);
//			ImportExcelUtil.filterEmpty(workExperienceExcels);
//			ImportResult workExperienceResult = StaffExcelCheckDataSon.checkWorkExperience(workExperienceExcels,codeList,staffFilesList,codeMap,isCover);
//			if(!workExperienceResult.isSuccess()){
//				CacheUtil.put(progress, progress, key, workExperienceResult.getMsg());
//				return new AsyncResult<>(workExperienceResult);
//			}
            //奖惩情况
            inputStream.reset();
            List<Object> rewardsPunishmentExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(5).headRowNumber(1).head(RewardsPunishmentsExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> rewardsPunishmentExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(rewardsPunishmentExcels);
            ImportResult rewardsPunishmentsResult = DshStaffManagementCheckDataSon.checkRewardsPunishments(rewardsPunishmentExcels);
            if (!rewardsPunishmentsResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, rewardsPunishmentsResult.getMsg());
                return new AsyncResult<>(rewardsPunishmentsResult);
            }
            //年度考核结果
            inputStream.reset();
            List<Object> annualAssessmentExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(6).headRowNumber(1).head(AnnualAssessmentExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> annualAssessmentExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(annualAssessmentExcels);
            ImportResult annualAssessResult = DshStaffManagementCheckDataSon.checkAnnualAssessment(annualAssessmentExcels);
            if (!annualAssessResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, annualAssessResult.getMsg());

                return new AsyncResult<>(annualAssessResult);
            }
            //家庭及社会关系
            inputStream.reset();
            List<Object> socialRelationExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(7).headRowNumber(1).head(SocialRelationsExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> socialRelationExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(socialRelationExcels);
            ImportResult socialRelationResult = DshStaffManagementCheckDataSon.checkSocialRelations(socialRelationExcels, codeMap);
            if (!socialRelationResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, socialRelationResult.getMsg());
                return new AsyncResult<>(socialRelationResult);
            }
            //从业资格证书
            inputStream.reset();
            List<Object> practitionerCertificateExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(8).headRowNumber(1).head(PractitionerCertificateExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> practitionerCertificateExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(practitionerCertificateExcels);
            ImportResult practitionerCertificateResult = DshStaffManagementCheckDataSon.checkPractitionerCertificate(practitionerCertificateExcels);
            if (!practitionerCertificateResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, practitionerCertificateResult.getMsg());

                return new AsyncResult<>(practitionerCertificateResult);
            }
            //专业技术资格
            inputStream.reset();
            List<Object> technicalExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(9).headRowNumber(1).head(TechnicalQualificationExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> technicalExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(technicalExcels);
            ImportResult technicalResult = DshStaffManagementCheckDataSon.checkTechnicalQualification(technicalExcels, codeMap);
            if (!technicalResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, technicalResult.getMsg());

                return new AsyncResult<>(technicalResult);
            }
            //近五年三个月以上培训情况
            inputStream.reset();
            List<Object> trainExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(10).headRowNumber(1).head(TrainExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> trainExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(trainExcels);
            ImportResult trainResult = DshStaffManagementCheckDataSon.checkTrain(trainExcels);
            if (!trainResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, trainResult.getMsg());

                return new AsyncResult<>(trainResult);
            }
            //侨眷情况
            inputStream.reset();
            List<Object> overseaExcels = new ArrayList<>();
            EasyExcel.read(inputStream).build()
                    .read(EasyExcel.readSheet(11).headRowNumber(1).head(OverseasMembersExcelBean.class)
                            .registerReadListener(new PageReadListener<>(list -> overseaExcels.addAll(list))).build());
            ImportExcelUtil.filterEmpty(overseaExcels);
            ImportResult overseaResult = DshStaffManagementCheckDataSon.checkOverseasMembers(overseaExcels, codeMap);
            if (!overseaResult.isSuccess()) {
                CacheUtil.put(progress, progress, key, overseaResult.getMsg());

                return new AsyncResult<>(overseaResult);
            }

            int count = 0;
            //主表存储
            for (Object object : staffReadExcels) {
                DshStaffManagement supervisor = (DshStaffManagement) ImportExcelUtil.getEntityByExcelBean((DshStaffManagementBean) object, new DshStaffManagement());
                StaffFiles staffFiles = (StaffFiles) ImportExcelUtil.getEntityByExcelBean((DshStaffManagementBean) object, new StaffFiles());
                staffFiles.setDocumentNumber(staffFiles.getStaffCode());
                //入库
                try {
                    supervisor.setStaffFilesVO(BeanUtil.copyProperties(staffFiles,StaffFilesVO.class));
                    this.submit(supervisor);
                    count++;
                    int value = count * 100 / size;
                    CacheUtil.put(progress, progress, key, value + 100);
                } catch (Exception e) {
                    log.error(ExceptionUtils.getStackTrace(e));
                    log.error(staffFiles + "导入失败");
                    CacheUtil.put(progress, progress, key, staffFiles + "导入失败");

                    return new AsyncResult<>(new ImportResult(false, staffFiles + "导入失败"));
                }
            }
            //任免记录子表
            HashMap<String, List<AppointRemove>> appointRemoveMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : appoiontExcels) {
                AppointRemove appointRemove = (AppointRemove) ImportExcelUtil.getEntityByExcelBean((AppointRemoveExcelBean) readExcels, new AppointRemove());
                if (appointRemoveMap.containsKey(appointRemove.getDef1())) {
                    appointRemoveMap.get(appointRemove.getDef1()).add(appointRemove);
                } else {
                    List<AppointRemove> value = new ArrayList<>();
                    value.add(appointRemove);
                    appointRemoveMap.put(appointRemove.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<AppointRemove>> entry : appointRemoveMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<AppointRemove> oldAppointRemove = appointRemoveService.ListByPid(staffFiles.getId());
                        if (oldAppointRemove.size() > 0) {
                            List<Long> ids = oldAppointRemove.parallelStream().map(AppointRemove::getId).collect(Collectors.toList());
                            appointRemoveService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        appointRemoveService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(appointRemoveErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, appointRemoveErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, appointRemoveErrMsg + "导入失败"));
            }

            //学历学位
            HashMap<String, List<EducationDegree>> educationDegreeMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : educationDegreeExcels) {
                EducationDegree educationDegree = (EducationDegree) ImportExcelUtil.getEntityByExcelBean((EducationDegreeExcelBean) readExcels, new EducationDegree());
                if (educationDegreeMap.containsKey(educationDegree.getDef1())) {
                    educationDegreeMap.get(educationDegree.getDef1()).add(educationDegree);
                } else {
                    List<EducationDegree> value = new ArrayList<>();
                    value.add(educationDegree);
                    educationDegreeMap.put(educationDegree.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<EducationDegree>> entry : educationDegreeMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<EducationDegree> oldEducationDegree = educationDegreeService.ListByPid(staffFiles.getId());
                        if (oldEducationDegree.size() > 0) {
                            List<Long> ids = oldEducationDegree.parallelStream().map(EducationDegree::getId).collect(Collectors.toList());
                            educationDegreeService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        educationDegreeService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(educationDegreeErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, educationDegreeErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, educationDegreeErrMsg + "导入失败"));
            }
            //工作经历
//			HashMap<String,List<WorkExperience>> workExperienceMap = new HashMap<>();
//			//对每条子表数据进行分组
//			for(Object readExcels:workExperienceExcels) {
//				WorkExperience workExperience = (WorkExperience) ImportExcelUtil.getEntityByExcelBean((WorkExperienceExcelBean) readExcels, new WorkExperience());
//				if (workExperienceMap.containsKey(workExperience.getDef1())) {
//					workExperienceMap.get(workExperience.getDef1()).add(workExperience);
//				} else {
//					List<WorkExperience> value = new ArrayList<>();
//					value.add(workExperience);
//					workExperienceMap.put(workExperience.getDef1(), value);
//				}
//			}
//			//入库
//			try{
//				for(HashMap.Entry<String,List<WorkExperience>> entry : workExperienceMap.entrySet()){
//					StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
//					//覆盖时删除原对应主表记录下属的所有旧子表记录
//					if(isCover) {
//						List<WorkExperience> oldWorkExperience = workExperienceService.ListByPid(staffFiles.getId());
//						if(oldWorkExperience.size()>0) {
//							List<Long> ids = oldWorkExperience.parallelStream().map(WorkExperience::getId).collect(Collectors.toList());
//							workExperienceService.deleteLogic(ids);
//						}
//					}
//					entry.getValue().forEach(x->{
//						x.setStaffFilesId(staffFiles.getId());
//						x.setDef1("");
//						workExperienceService.saveOrUpdate(x);
//					});
//				}
//			}catch(Exception e){
//				log.error(workExperienceErrMsg + "导入失败");
//				CacheUtil.put(progress, progress, key,workExperienceErrMsg + "导入失败");
//
//				return new AsyncResult<>(new ImportResult(false,workExperienceErrMsg + "导入失败"));
//			}
            //奖惩情况
            HashMap<String, List<RewardsPunishments>> rewardsPunishmentsMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : rewardsPunishmentExcels) {
                RewardsPunishments rewardsPunishments = (RewardsPunishments) ImportExcelUtil.getEntityByExcelBean((RewardsPunishmentsExcelBean) readExcels, new RewardsPunishments());
                if (rewardsPunishmentsMap.containsKey(rewardsPunishments.getDef1())) {
                    rewardsPunishmentsMap.get(rewardsPunishments.getDef1()).add(rewardsPunishments);
                } else {
                    List<RewardsPunishments> value = new ArrayList<>();
                    value.add(rewardsPunishments);
                    rewardsPunishmentsMap.put(rewardsPunishments.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<RewardsPunishments>> entry : rewardsPunishmentsMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<RewardsPunishments> oldRewardsPunishments = rewardsPunishmentsService.ListByPid(staffFiles.getId());
                        if (oldRewardsPunishments.size() > 0) {
                            List<Long> ids = oldRewardsPunishments.parallelStream().map(RewardsPunishments::getId).collect(Collectors.toList());
                            rewardsPunishmentsService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        rewardsPunishmentsService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(rewardsPunishmentsErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, rewardsPunishmentsErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, rewardsPunishmentsErrMsg + "导入失败"));
            }
            //年度考核结果
            HashMap<String, List<AnnualAssessment>> annualAssessmentMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : annualAssessmentExcels) {
                AnnualAssessment annualAssessment = (AnnualAssessment) ImportExcelUtil.getEntityByExcelBean((AnnualAssessmentExcelBean) readExcels, new AnnualAssessment());
                if (annualAssessmentMap.containsKey(annualAssessment.getDef1())) {
                    annualAssessmentMap.get(annualAssessment.getDef1()).add(annualAssessment);
                } else {
                    List<AnnualAssessment> value = new ArrayList<>();
                    value.add(annualAssessment);
                    annualAssessmentMap.put(annualAssessment.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<AnnualAssessment>> entry : annualAssessmentMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<AnnualAssessment> oldAnnualAssessment = annualAssessmentService.ListByPid(staffFiles.getId());
                        if (oldAnnualAssessment.size() > 0) {
                            List<Long> ids = oldAnnualAssessment.parallelStream().map(AnnualAssessment::getId).collect(Collectors.toList());
                            annualAssessmentService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        annualAssessmentService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(annualAssessmentErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, annualAssessmentErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, annualAssessmentErrMsg + "导入失败"));
            }
            //家庭及社会关系
            HashMap<String, List<SocialRelations>> socialRelationsMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : socialRelationExcels) {
                SocialRelations socialRelations = (SocialRelations) ImportExcelUtil.getEntityByExcelBean((SocialRelationsExcelBean) readExcels, new SocialRelations());
                if (socialRelationsMap.containsKey(socialRelations.getDef1())) {
                    socialRelationsMap.get(socialRelations.getDef1()).add(socialRelations);
                } else {
                    List<SocialRelations> value = new ArrayList<>();
                    value.add(socialRelations);
                    socialRelationsMap.put(socialRelations.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<SocialRelations>> entry : socialRelationsMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<SocialRelations> oldSocialRelations = socialRelationsService.ListByPid(staffFiles.getId());
                        if (oldSocialRelations.size() > 0) {
                            List<Long> ids = oldSocialRelations.parallelStream().map(SocialRelations::getId).collect(Collectors.toList());
                            socialRelationsService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        socialRelationsService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(socialRelationsErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, socialRelationsErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, socialRelationsErrMsg + "导入失败"));
            }
            //从业资格证书
            HashMap<String, List<PractitionerCertificate>> practitionerCertificateMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : practitionerCertificateExcels) {
                PractitionerCertificate practitionerCertificate = (PractitionerCertificate) ImportExcelUtil.getEntityByExcelBean((PractitionerCertificateExcelBean) readExcels, new PractitionerCertificate());
                if (practitionerCertificateMap.containsKey(practitionerCertificate.getDef1())) {
                    practitionerCertificateMap.get(practitionerCertificate.getDef1()).add(practitionerCertificate);
                } else {
                    List<PractitionerCertificate> value = new ArrayList<>();
                    value.add(practitionerCertificate);
                    practitionerCertificateMap.put(practitionerCertificate.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<PractitionerCertificate>> entry : practitionerCertificateMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<PractitionerCertificate> oldPractitionerCertificate = practitionerCertificateService.ListByPid(staffFiles.getId());
                        if (oldPractitionerCertificate.size() > 0) {
                            List<Long> ids = oldPractitionerCertificate.parallelStream().map(PractitionerCertificate::getId).collect(Collectors.toList());
                            practitionerCertificateService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        practitionerCertificateService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(practitionerCertificateErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, practitionerCertificateErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, practitionerCertificateErrMsg + "导入失败"));
            }
            //专业技术资格
            HashMap<String, List<TechnicalQualification>> technicalQualificationMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : technicalExcels) {
                TechnicalQualification technicalQualification = (TechnicalQualification) ImportExcelUtil.getEntityByExcelBean((TechnicalQualificationExcelBean) readExcels, new TechnicalQualification());
                if (technicalQualificationMap.containsKey(technicalQualification.getDef1())) {
                    technicalQualificationMap.get(technicalQualification.getDef1()).add(technicalQualification);
                } else {
                    List<TechnicalQualification> value = new ArrayList<>();
                    value.add(technicalQualification);
                    technicalQualificationMap.put(technicalQualification.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<TechnicalQualification>> entry : technicalQualificationMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<TechnicalQualification> oldTechnicalQualification = technicalQualificationService.ListByPid(staffFiles.getId());
                        if (oldTechnicalQualification.size() > 0) {
                            List<Long> ids = oldTechnicalQualification.parallelStream().map(TechnicalQualification::getId).collect(Collectors.toList());
                            technicalQualificationService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        technicalQualificationService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(technicalQualificationErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, technicalQualificationErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, technicalQualificationErrMsg + "导入失败"));
            }
            //近五年三个月以上培训情况
            HashMap<String, List<Train>> trainMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : trainExcels) {
                Train train = (Train) ImportExcelUtil.getEntityByExcelBean((TrainExcelBean) readExcels, new Train());
                if (trainMap.containsKey(train.getDef1())) {
                    trainMap.get(train.getDef1()).add(train);
                } else {
                    List<Train> value = new ArrayList<>();
                    value.add(train);
                    trainMap.put(train.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<Train>> entry : trainMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<Train> oldTrain = trainService.ListByPid(staffFiles.getId());
                        if (oldTrain.size() > 0) {
                            List<Long> ids = oldTrain.parallelStream().map(Train::getId).collect(Collectors.toList());
                            trainService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        trainService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(trainErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, trainErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, trainErrMsg + "导入失败"));
            }
            //侨眷情况
            HashMap<String, List<OverseasMembers>> overseasMembersMap = new HashMap<>();
            //对每条子表数据进行分组
            for (Object readExcels : overseaExcels) {
                OverseasMembers overseasMembers = (OverseasMembers) ImportExcelUtil.getEntityByExcelBean((OverseasMembersExcelBean) readExcels, new OverseasMembers());
                if (overseasMembersMap.containsKey(overseasMembers.getDef1())) {
                    overseasMembersMap.get(overseasMembers.getDef1()).add(overseasMembers);
                } else {
                    List<OverseasMembers> value = new ArrayList<>();
                    value.add(overseasMembers);
                    overseasMembersMap.put(overseasMembers.getDef1(), value);
                }
            }
            //入库
            try {
                for (HashMap.Entry<String, List<OverseasMembers>> entry : overseasMembersMap.entrySet()) {
                    StaffFiles staffFiles = searchStaffByStaffCode(entry.getKey());
                    //覆盖时删除原对应主表记录下属的所有旧子表记录
                    if (isCover) {
                        List<OverseasMembers> oldOverseasMembers = overseasMembersService.ListByPid(staffFiles.getId());
                        if (oldOverseasMembers.size() > 0) {
                            List<Long> ids = oldOverseasMembers.parallelStream().map(OverseasMembers::getId).collect(Collectors.toList());
                            overseasMembersService.deleteLogic(ids);
                        }
                    }
                    entry.getValue().forEach(x -> {
                        x.setStaffFilesId(staffFiles.getId());
                        x.setDef1("");
                        overseasMembersService.saveOrUpdate(x);
                    });
                }
            } catch (Exception e) {
                log.error(overseasMembersErrMsg + "导入失败");
                CacheUtil.put(progress, progress, key, overseasMembersErrMsg + "导入失败");

                return new AsyncResult<>(new ImportResult(false, overseasMembersErrMsg + "导入失败"));
            }
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            log.error("解析失败");
            CacheUtil.put(progress, progress, key, "解析失败");

            return new AsyncResult<>(new ImportResult(false, "解析失败"));
        }
        CacheUtil.put(progress, progress, key, "导入成功");
        return new AsyncResult<>(new ImportResult(true, "导入成功"));
    }

    @Override
    public void exportTemplateExcel(HttpServletResponse response, OutputStream os) {
        try {
            String fname = "董事会档案导入模板";
            os = response.getOutputStream();//取得输出流
            response.reset();//清空输出流
            //下面是对中文文件名的处理
            response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式
            InputStream inputStream = this.getClass().getResourceAsStream("/exceldemo/dshstafffiles.xlsx");
            fname = java.net.URLEncoder.encode(fname, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname.getBytes("UTF-8"), "UTF-8") + ".xlsx");

            response.setContentType("application/msexcel");//定义输出类型
            int len;
            byte[] b = new byte[2048];
            while ((len = inputStream.read(b)) != -1) {
                os.write(b, 0, len);
            }
            response.setHeader("Content-Length", String.valueOf(inputStream.available()));

            inputStream.close();
            os.close();
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
        }

    }

    @Override
    public String exportImportFile() {
        String key = System.currentTimeMillis() + StringPool.DASH + UUID.randomUUID().toString().replace(StringPool.DASH, "");
        String fileName = key + ".xlsx";
        String exportPath = environment.getProperty("excel.path");

        /**
         * 导出路径不存在则创建
         */
        File file = new File(exportPath);
        if (!file.exists()) {
            file.mkdirs();
        }
        InputStream inputStream = this.getClass().getResourceAsStream("/exceldemo/dshStaffMangementExport.xlsx");
        ExcelWriter excelWriter = EasyExcel.write(exportPath + fileName).withTemplate(inputStream).build();

        // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
        // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
        // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存

        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        // 表二
        WriteSheet staffFilesWriteSheet = EasyExcel.writerSheet(1).build();

        Query query = new Query();
        query.setFlag(1);
        QueryWrapper<DshStaffManagement> queryWrapper = SearchParamEntiy.getQueryWrapper(
                Condition.getQueryWrapper(new DshStaffManagement()), query.getSearch()
        );
        TokenSearch<DshStaffManagement> tokenSearch = new TokenSearch<>();

        tokenSearch.addTokenQuery(queryWrapper, query);
        queryWrapper.lambda().orderByAsc(DshStaffManagement::getSupervisorPositionId);
        queryWrapper.lambda().orderByDesc(DshStaffManagement::getCreateTime);
        List<DshStaffManagement> dshList = this.list(queryWrapper);
        Map<Long, DshStaffManagement> dshMap = dshList.stream().collect(Collectors.toMap(DshStaffManagement::getStaffFilesId, a -> a));
        List<Long> staffFilesIds = dshList.stream().map(DshStaffManagement::getStaffFilesId).collect(Collectors.toList());
        List<StaffFiles> staffList = staffFilesService.list(new LambdaQueryWrapper<StaffFiles>()
                .in(CollectionUtil.isNotEmpty(staffFilesIds), StaffFiles::getId, staffFilesIds));
        List<StaffFilesVO> staffFilesVOS = staffFilesWrapper.entityToVO(staffList);
        List<DshStaffExport> exportList = BeanUtil.copyToList(staffFilesVOS,DshStaffExport.class);
        Map<String, String> superPositionMap = baseInfoService.getChildMap(ValueSetConstant.DIRECTORSHIP);
        exportList.forEach(e ->{
            if(dshMap.containsKey(e.getId())){
                e.setIsOutSupervisorName(dshMap.get(e.getId()).getIsOutSupervisor().equals(2) ? "是" : "否");
                e.setSupervisorPositionName(superPositionMap.get(dshMap.get(e.getId()).getSupervisorPositionId()));
            }
            if (null != e.getIsReserveCadre() && 2 == e.getIsReserveCadre()) {
                e.setIsReserveCadreName("是");
            } else {
                e.setIsReserveCadreName("否");
            }
            if (null != e.getIsCorporateTalent() && 2 == e.getIsCorporateTalent()) {
                e.setIsCorporateTalentName("是");
            } else {
                e.setIsCorporateTalentName("否");
            }
        });
        excelWriter.fill(exportList, fillConfig, staffFilesWriteSheet);

        Map<Long, List<AppointRemoveVO>> appointRemoveVOMap;
        Map<Long, List<EducationDegreeVO>> educationDegreeVOMap;
        Map<Long, List<RewardsPunishmentsVO>> rewardsPunishmentsVOMap;
        Map<Long, List<AnnualAssessmentVO>> annualAssessmentVOMap;
        Map<Long, List<SocialRelationsVO>> socialRelationsVOMap;
        Map<Long, List<PractitionerCertificateVO>> practitionerCertificateVOMap;
        Map<Long, List<TechnicalQualificationVO>> technicalQualificationVOMap;
        Map<Long, List<TrainVO>> trainVOMap;
        Map<Long, List<OverseasMembersVO>> overseasMembersVOMap;
        if(CollectionUtil.isEmpty(staffFilesIds)){
            appointRemoveVOMap = new HashMap<>();
            educationDegreeVOMap = new HashMap<>();
            rewardsPunishmentsVOMap = new HashMap<>();
            annualAssessmentVOMap = new HashMap<>();
            socialRelationsVOMap = new HashMap<>();
            practitionerCertificateVOMap = new HashMap<>();
            technicalQualificationVOMap = new HashMap<>();
            trainVOMap = new HashMap<>();
            overseasMembersVOMap = new HashMap<>();
        }else {
            // 任免记录 3
            appointRemoveVOMap = appointRemoveWrapper
                    .entityToVO(appointRemoveService.list(new LambdaQueryWrapper<AppointRemove>()
                            .in(AppointRemove::getStaffFilesId, staffFilesIds)))
                    .stream().collect(Collectors.groupingBy(AppointRemoveVO::getStaffFilesId));

            // 学历学位
            List<EducationDegreeVO> eduVoList = educationDegreeWrapper.entityToVO(educationDegreeService.list(new LambdaQueryWrapper<EducationDegree>()
                    .in(EducationDegree::getStaffFilesId, staffFilesIds)));
            eduVoList.forEach(e ->{
                if (null != e.getIsHighestEducational() && 2 == e.getIsHighestEducational()) {
                    e.setIsHighestEducationalName("是");
                } else {
                    e.setIsHighestEducationalName("否");
                }
                if (null != e.getIsHighestDegree() && 2 == e.getIsHighestDegree()) {
                    e.setIsHighestDegreeName("是");
                } else {
                    e.setIsHighestDegreeName("否");
                }
            });
            educationDegreeVOMap = eduVoList
                    .stream().collect(Collectors.groupingBy(EducationDegreeVO::getStaffFilesId));

            // 奖惩情况
            rewardsPunishmentsVOMap = rewardsPunishmentsWrapper
                    .entityToVO(rewardsPunishmentsService.list(new LambdaQueryWrapper<RewardsPunishments>()
                            .in(RewardsPunishments::getStaffFilesId, staffFilesIds)))
                    .stream().collect(Collectors.groupingBy(RewardsPunishmentsVO::getStaffFilesId));

            // 年度考核结果
            annualAssessmentVOMap = annualAssessmentWrapper
                    .entityToVO(annualAssessmentService.list(new LambdaQueryWrapper<AnnualAssessment>()
                            .in(AnnualAssessment::getStaffFilesId, staffFilesIds)))
                    .stream().collect(Collectors.groupingBy(AnnualAssessmentVO::getStaffFilesId));

            // 家庭及社会关系
            socialRelationsVOMap = socialRelationsWrapper
                    .entityToVO(socialRelationsService.list(new LambdaQueryWrapper<SocialRelations>()
                            .in(SocialRelations::getStaffFilesId, staffFilesIds)))
                    .stream().collect(Collectors.groupingBy(SocialRelationsVO::getStaffFilesId));

            // 从业资格证书
            practitionerCertificateVOMap = practitionerCertificateWrapper
                    .entityToVO(practitionerCertificateService.list(new LambdaQueryWrapper<PractitionerCertificate>()
                            .in(PractitionerCertificate::getStaffFilesId, staffFilesIds)))
                    .stream().collect(Collectors.groupingBy(PractitionerCertificateVO::getStaffFilesId));

            // 专业技术资格
            technicalQualificationVOMap = technicalQualificationWrapper
                    .entityToVO(technicalQualificationService.list(new LambdaQueryWrapper<TechnicalQualification>()
                            .in(TechnicalQualification::getStaffFilesId, staffFilesIds)))
                    .stream().collect(Collectors.groupingBy(TechnicalQualificationVO::getStaffFilesId));

            // 近五年三月以上培训情况
            trainVOMap = trainWrapper
                    .entityToVO(trainService.list(new LambdaQueryWrapper<Train>()
                            .in(Train::getStaffFilesId, staffFilesIds)))
                    .stream().collect(Collectors.groupingBy(TrainVO::getStaffFilesId));

            // 侨眷情况
            overseasMembersVOMap = overseasMembersWrapper
                    .entityToVO(overseasMembersService.list(new LambdaQueryWrapper<OverseasMembers>()
                            .in(OverseasMembers::getStaffFilesId, staffFilesIds)))
                    .stream().collect(Collectors.groupingBy(OverseasMembersVO::getStaffFilesId));
        }


        List<EducationDegreeVO> educationDegreeVOS = new ArrayList<>();
        List<AppointRemoveVO> appointRemoveVOS = new ArrayList<>();
        List<RewardsPunishmentsVO> rewardsPunishmentsVOS = new ArrayList<>();
        List<AnnualAssessmentVO> annualAssessmentVOS = new ArrayList<>();
        List<SocialRelationsVO> socialRelationsVOS = new ArrayList<>();
        List<PractitionerCertificateVO> practitionerCertificateVOS = new ArrayList<>();
        List<TechnicalQualificationVO> technicalQualificationVOS = new ArrayList<>();
        List<TrainVO> trainVOS = new ArrayList<>();
        List<OverseasMembersVO> overseasMembersVOS = new ArrayList<>();

        for (StaffFilesVO staffFilesVO : staffFilesVOS) {
            Long staffFilesId = staffFilesVO.getId();
            String staffCode = staffFilesVO.getStaffCode();
            if (educationDegreeVOMap.containsKey(staffFilesId)) {
                educationDegreeVOS.addAll(educationDegreeVOMap.get(staffFilesId).stream()
                        .map(educationDegreeVO -> {
                            educationDegreeVO.setStaffCode(staffCode);
                            return educationDegreeVO;
                        }).collect(Collectors.toList()));
            }

            if (appointRemoveVOMap.containsKey(staffFilesId)) {
                appointRemoveVOS.addAll(appointRemoveVOMap.get(staffFilesId).stream()
                        .map(appointRemoveVO -> {
                            appointRemoveVO.setStaffCode(staffCode);
                            return appointRemoveVO;
                        }).collect(Collectors.toList()));
            }

            if (rewardsPunishmentsVOMap.containsKey(staffFilesId)) {
                rewardsPunishmentsVOS.addAll(rewardsPunishmentsVOMap.get(staffFilesId).stream()
                        .map(rewardsPunishmentsVO -> {
                            rewardsPunishmentsVO.setStaffCode(staffCode);
                            return rewardsPunishmentsVO;
                        }).collect(Collectors.toList()));
            }

            if (annualAssessmentVOMap.containsKey(staffFilesId)) {
                annualAssessmentVOS.addAll(annualAssessmentVOMap.get(staffFilesId).stream()
                        .map(annualAssessmentVO -> {
                            annualAssessmentVO.setStaffCode(staffCode);
                            return annualAssessmentVO;
                        }).collect(Collectors.toList()));
            }

            if (socialRelationsVOMap.containsKey(staffFilesId)) {
                socialRelationsVOS.addAll(socialRelationsVOMap.get(staffFilesId).stream()
                        .map(socialRelationsVO -> {
                            socialRelationsVO.setStaffCode(staffCode);
                            return socialRelationsVO;
                        }).collect(Collectors.toList()));
            }

            if (practitionerCertificateVOMap.containsKey(staffFilesId)) {
                practitionerCertificateVOS.addAll(practitionerCertificateVOMap.get(staffFilesId).stream()
                        .map(practitionerCertificateVO -> {
                            practitionerCertificateVO.setStaffCode(staffCode);
                            return practitionerCertificateVO;
                        }).collect(Collectors.toList()));
            }

            if (technicalQualificationVOMap.containsKey(staffFilesId)) {
                technicalQualificationVOS.addAll(technicalQualificationVOMap.get(staffFilesId).stream()
                        .map(technicalQualificationVO -> {
                            technicalQualificationVO.setStaffCode(staffCode);
                            return technicalQualificationVO;
                        }).collect(Collectors.toList()));
            }

            if (trainVOMap.containsKey(staffFilesId)) {
                trainVOS.addAll(trainVOMap.get(staffFilesId).stream()
                        .map(trainVO -> {
                            trainVO.setStaffCode(staffCode);
                            return trainVO;
                        }).collect(Collectors.toList()));
            }

            if (overseasMembersVOMap.containsKey(staffFilesId)) {
                overseasMembersVOS.addAll(overseasMembersVOMap.get(staffFilesId).stream()
                        .map(overseasMembersVO -> {
                            overseasMembersVO.setStaffCode(staffCode);
                            return overseasMembersVO;
                        }).collect(Collectors.toList()));
            }

        }


        WriteSheet appointRemoveWriteSheet = EasyExcel.writerSheet(3).build();
        excelWriter.fill(appointRemoveVOS, fillConfig, appointRemoveWriteSheet);
        WriteSheet educationDegreeWriteSheet = EasyExcel.writerSheet(4).build();
        excelWriter.fill(educationDegreeVOS, fillConfig, educationDegreeWriteSheet);
        WriteSheet rewardsPunishmentsWriteSheet = EasyExcel.writerSheet(5).build();
        excelWriter.fill(annualAssessmentVOS, fillConfig, rewardsPunishmentsWriteSheet);
        WriteSheet annualAssessmentWriteSheet = EasyExcel.writerSheet(6).build();
        excelWriter.fill(annualAssessmentVOS, fillConfig, annualAssessmentWriteSheet);
        WriteSheet socialRelationsWriteSheet = EasyExcel.writerSheet(7).build();
        excelWriter.fill(socialRelationsVOS, fillConfig, socialRelationsWriteSheet);
        WriteSheet practitionerCertificateWriteSheet = EasyExcel.writerSheet(8).build();
        excelWriter.fill(practitionerCertificateVOS, fillConfig, practitionerCertificateWriteSheet);
        WriteSheet technicalQualificationWriteSheet = EasyExcel.writerSheet(9).build();
        excelWriter.fill(technicalQualificationVOS, fillConfig, technicalQualificationWriteSheet);
        WriteSheet trainWriteSheet = EasyExcel.writerSheet(10).build();
        excelWriter.fill(trainVOS, fillConfig, trainWriteSheet);
        WriteSheet overseasMembersWriteSheet = EasyExcel.writerSheet(11).build();
        excelWriter.fill(overseasMembersVOS, fillConfig, overseasMembersWriteSheet);
        excelWriter.finish();

        PageData org = orgzationService.getCompanyNameById(TokenUtil.getTokenOrgId());
        String companyName = "";
        if (org != null) {
            companyName = org.getString("name");
        }
        String excelName = companyName + "_董事会档案备份";
        fileNameMap.put(key, excelName);
        return key;
    }

    @Override
    public void download(String key, HttpServletResponse response) {
        if (!fileNameMap.containsKey(key)) {
            log.error("BaseInvestmentPlanServiceImpl 导出excel失败--------> key " + key);
            return;
        }
        String excelName = fileNameMap.get(key);
        String excelPath = environment.getProperty("excel.path") + "/" + key + ".xlsx";
        FileTools.exportExcel(excelPath, excelName, response);
        fileNameMap.remove(key);
    }
}
